package henu.dao.impl;
/**
 *  行车证信息
 * @author 王竞赛
 *
 * 2014-1-20
 *
 */
import java.sql.SQLException;
import java.util.List;

import net.sf.json.JSONArray;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import henu.bean.BusinessMessage;
import henu.bean.Car;
import henu.bean.DrivingCar;
import henu.dao.DrivingCarDao;
import henu.dao.factory.DaoFactory;
import henu.util.Dbcp;

public class DrivingCarDaoImpl implements DrivingCarDao {

	@Override
	public int add(DrivingCar dc) {
		// TODO 自动生成的方法存根
				int result = 0;
				try {                               
					String sql = "insert into DRIVINGCAR (carid, drivingId,vehicleLicense, vehicleType,owners, address, useCharacter, models, vin, engineid, registerDate, doclc, menNumber, totalWeight, weight, checkWeight, sizes, towWeight, validations, copyDrivingCar, memo) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
					QueryRunner qr = new QueryRunner(Dbcp.getDataSource());
					Object[] params = {dc.getCarid(),dc.getDrivingId(),dc.getVehicleLicense(),dc.getVehicleType(),dc.getOwners(),dc.getAddress(),dc.getUseCharacter(),dc.getModels(),dc.getVin(),dc.getEngineid(),dc.getRegisterDate(),dc.getDoclc(),dc.getMenNumber(),dc.getTotalWeight(),dc.getWeight(),dc.getCheckWeight(),dc.getSizes(),dc.getTowWeight(),dc.getValidations(),dc.getCopyDrivingCar(),dc.getMemo()};
					result = qr.update(sql, params);
				} catch (SQLException e) {
					e.printStackTrace();
				}
				return result;
	}

	@Override
	public int deleteById(int drid) {
		int result = 0;
		String sql = "delete from DRIVINGCAR where drid=?";
		QueryRunner qr = new QueryRunner(Dbcp.getDataSource());
		try {
			result = qr.update(sql, drid);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return result;
	}

	@Override
	public int update(int drid,DrivingCar dc) {
		int result = 0;
		try {                                                                                                                                                                                                                 /*contactaddress,registmoney,registeroffice,businesslimit,allowbusinesscard,postcode,basicaccoutopen,basicaccoutid,companyprincipal,memo1,memo2,memo3,memo4*/
			QueryRunner qr =DaoFactory.getRunner();
			String sql = "update  DRIVINGCAR  set drivingId=?, vehicleLicense=?, vehicleType=?, owners=?, address=?, useCharacter=?, models=?, vin=?, engineid=?, registerDate=?, doclc=?, menNumber=?, totalWeight=?, weight=?, checkWeight=?, sizes=?, towWeight=?, validations=?, copyDrivingCar=?, memo=?  where drid = ?";
			System.out.println(sql);
			Object[] params = {dc.getDrivingId(),dc.getVehicleLicense(),dc.getVehicleType(),dc.getOwners(),dc.getAddress(),dc.getUseCharacter(),dc.getModels(),dc.getVin(),dc.getEngineid(),dc.getRegisterDate(),dc.getDoclc(),dc.getMenNumber(),dc.getTotalWeight(),dc.getWeight(),dc.getCheckWeight(),dc.getSizes(),dc.getTowWeight(),dc.getValidations(),dc.getCopyDrivingCar(),dc.getMemo(),drid};
			result = qr.update(sql, params);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return result;
	}

	@Override
	public DrivingCar findById(int drid) {
		DrivingCar dr = new DrivingCar();
		try {
			QueryRunner qr = new QueryRunner(Dbcp.getDataSource());
			String sql = "select * from DRIVINGCAR where drid = ?";
		
			dr = qr.query(sql, new BeanHandler<DrivingCar>(DrivingCar.class), drid);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return dr;
	}

	@Override
	public List<DrivingCar> findAllByCarId(String property, String key,String idCard,
			String order, String sort,int start,int end) {
		// TODO Auto-generated method stub
		List<DrivingCar> list=null;
		try {
			QueryRunner qr=new QueryRunner(Dbcp.getDataSource());
			String sql="SELECT * FROM (SELECT tt.*, ROWNUM ro FROM (select dc.* from drivingcar dc,carinformation c where "+property+"='"+key+"' and c.userid=? and c.carid=dc.carid order by "+order+" "+sort+") tt WHERE ROWNUM <="+end+") WHERE ro > "+start+"";
			list=qr.query(sql, new BeanListHandler<DrivingCar>(DrivingCar.class),idCard);
		} catch (Exception e) {
			// TODO: handle exception
		}
		return list;
	}
	@Override
	public DrivingCar findByDrivingIdOrVehicleLicense(String drivingId,
			String vehicleLicense) {
				DrivingCar dc=null;
				try {
					QueryRunner qr=new QueryRunner(Dbcp.getDataSource());
					String sql="select * from drivingcar  where drivingId=? or vehicleLicense=?";
					Object[] params={drivingId,vehicleLicense};
					dc=qr.query(sql, new BeanHandler<DrivingCar>(DrivingCar.class),params);
				} catch (Exception e) {
					e.printStackTrace();
				}
				return dc;
	}
	@Override
	public Car findByIdCardAndVehicleLicense(String idCard,	String vehicleLicense) {
		// TODO Auto-generated method stub
		Car c=null;
		try {
			QueryRunner qr=new QueryRunner(Dbcp.getDataSource());
			String sql="select * from carinformation  where userid=? and vehicleLicense=?";
			Object[] params={idCard,vehicleLicense};
			c=qr.query(sql, new BeanHandler<Car>(Car.class),params);
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return c;
	}
	
	@Override
	public List<DrivingCar> findAllByComid(String property,String key, int comid, String order, String sort,int start, int end) {
		List<DrivingCar> list=null;
		try {
			QueryRunner qr=new QueryRunner(Dbcp.getDataSource());
			String sql="SELECT * FROM (SELECT tt.*, ROWNUM ro FROM (select dc.* from drivingcar dc,carinformation c where "+property+"='"+key+"' and c.comid=? and c.carid=dc.carid order by "+order+" "+sort+") tt WHERE ROWNUM <="+end+") WHERE ro > "+start+"";
			list=qr.query(sql, new BeanListHandler<DrivingCar>(DrivingCar.class),comid);
		} catch (Exception e) {
		}
		return list;
	}
	
	@Override
	public List<DrivingCar> findAll() {
		String sql = "select * from DRIVINGCAR";
		QueryRunner qr = new QueryRunner(Dbcp.getDataSource());
		List<DrivingCar> list = null;
		try {
			list = qr.query(sql, new BeanListHandler<DrivingCar>(
					DrivingCar.class));
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public List<DrivingCar> findByProperty(String property, String key,
			String order, String sort, int start, int end) {
		List<DrivingCar> list = null;
		String sql = "SELECT * FROM (SELECT tt.*, ROWNUM ro FROM (select * from DRIVINGCAR where "
				+ property
				+ "='"
				+ key
				+ "'  order by "
				+ order
				+ " "
				+ sort
				+ ") tt WHERE ROWNUM <=" + end + ") WHERE ro > " + start + "";
		QueryRunner runner = new QueryRunner(Dbcp.getDataSource());
		try {
			list = runner.query(sql, new BeanListHandler<DrivingCar>(
					DrivingCar.class));
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

	
}
